package cn.com.headfree.salary.dao.impl;

import java.sql.Timestamp;
import java.util.*;

import cn.com.headfree.entity.*;
import cn.com.headfree.vo.DealFileVo;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;
import com.gta.edu.sdk.util.StringUtil;
import cn.com.headfree.entity.isc.User;
import cn.com.headfree.salary.dao.SalaryDao;


@Repository("salaryDao")
public class SalaryDaoImpl extends BaseDaoImpl<TSalaryInfo> implements SalaryDao {

	@Override
	public void saveSalaryInfo(TSalaryInfo tSalaryInfo) {
		String sql = "insert into t_salary_info (id,rule_id,erp_no,user_name,year,audit_status,audit_opt,org_id,org_name,creater_id,creater_name,create_time,updater_id,updater_name,update_time,del_sign) values ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append("(#{id,jdbcType=VARCHAR},").append("#{rule_id,jdbcType=VARCHAR},")
				.append("#{erp_no,jdbcType=VARCHAR},").append("#{user_name,jdbcType=VARCHAR},")
				.append("#{year,jdbcType=INTEGER},").append("#{audit_status,jdbcType=VARCHAR},")
				.append("#{audit_opt,jdbcType=VARCHAR},").append("#{org_id,jdbcType=VARCHAR},")
				.append("#{org_name,jdbcType=VARCHAR},").append("#{creater_id,jdbcType=VARCHAR},")
				.append("#{creater_name,jdbcType=VARCHAR},").append("#{create_time,jdbcType=DATE},")
				.append("#{updater_id,jdbcType=VARCHAR},").append("#{updater_name,jdbcType=VARCHAR},")
				.append("#{update_time,jdbcType=DATE},").append("#{del_sign,jdbcType=INTEGER})");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", tSalaryInfo.getId());
		params.put("rule_id", tSalaryInfo.getRuleId());
		params.put("erp_no", tSalaryInfo.getErpNo());
		params.put("user_name", tSalaryInfo.getUserName());
		params.put("year", tSalaryInfo.getYear());
		params.put("audit_status", tSalaryInfo.getAuditStatus());
		params.put("audit_opt", tSalaryInfo.getAuditOpt());
		params.put("org_id", tSalaryInfo.getOrgId());
		params.put("org_name", tSalaryInfo.getOrgName());
		params.put("creater_id", tSalaryInfo.getCreaterId());
		params.put("creater_name", tSalaryInfo.getCreaterName());
		params.put("create_time", new Timestamp(new Date().getTime()));
		params.put("updater_id", tSalaryInfo.getUpdaterId());
		params.put("updater_name", tSalaryInfo.getUpdaterName());
		params.put("update_time", new Timestamp(new Date().getTime()));
		params.put("del_sign", tSalaryInfo.getDelSign());
		this.executeSql(sqlBuilder.toString(), params);
	}

	@Override
	public TOrgDept getOrgNameByOrgId(String baseOrgId) {
		StringBuilder sqlBuilder = new StringBuilder(
				"select name FROM t_org_dept ");
		sqlBuilder.append(" where id=? ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		arrayList.add(baseOrgId);
		return this.findUnique(TOrgDept.class, sqlBuilder.toString(), arrayList.toArray());
	}


	@Override
	public void getPageSalaryInfo(Page<TSalaryInfo> page, String year, User user) {
		ArrayList<Object> arrayList = new ArrayList<Object>();
		String sql = "select t.*,o.is_over from t_salary_info t " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" LEFT JOIN t_point_set_over o on t.year = o.year and t.org_id = o.org_id ");
		sqlBuilder.append(" where t.del_sign = 0 ");
		sqlBuilder.append(" and t.erp_no=? ");
		arrayList.add(user.getFax());
		if (!StringUtil.isNullOrEmpty(year)) {
			sqlBuilder.append(" AND t.year= ? ");
			arrayList.add(year);
		}
		sqlBuilder.append(" order by t.create_time desc ");
		this.findPageBySql(TSalaryInfo.class, sqlBuilder.toString(), page, arrayList.toArray());
	}


	@Override
	public void getPageSalaryInfoQuery(Page<TSalaryInfo> page,String baseOrgId, String userName, String erpNo, Integer year, String auditStatus) {
		ArrayList<Object> arrayList = new ArrayList<Object>();
		String sql = "select distinct TEBI.id AS id,TEBI.NAME AS user_name,TEBI.erp_no AS erp_no,a.year as year,TDER.org_id,case " +
				" when a.erp_no is not null then '已申报' " +
				" else '未申报'" +
				" end as audit_status FROM t_emp_base_info TEBI " +
				" LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no " +
				" LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id " +
				" LEFT JOIN t_org_dept TOD2 ON TOD2.id = TDER.org_id " +
				" left join t_salary_info a on a.del_sign = 0  and a.erp_no=TEBI.erp_no and a.audit_status!='2' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		if (null!=year) {
			sqlBuilder.append(" AND a.year =? ");
			arrayList.add(year);
		}
		sqlBuilder.append(" WHERE TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
		sqlBuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
		arrayList.add(baseOrgId);
		if (StringUtils.isNotBlank(userName)) {
			sqlBuilder.append(" AND TEBI.NAME LIKE concat('%',?,'%') ");
			arrayList.add(userName);
		}
		if (StringUtils.isNotBlank(erpNo)) {
			sqlBuilder.append(" AND TEBI.erp_no LIKE concat('%',?,'%') ");
			arrayList.add(erpNo);
		}
		/*if (null!=year) {
			sqlBuilder.append(" AND a.year =? ");
			arrayList.add(year);
		}*/
		if (StringUtils.isNotBlank(auditStatus)) {
			if(auditStatus.equals("0")){
				//已申报
				sqlBuilder.append(" AND a.erp_no is not null ");
			}else{
                //未申报
				sqlBuilder.append(" AND a.erp_no is null ");
			}
		}
		sqlBuilder.append(" order by a.year desc,a.erp_no desc,TOD.id ");
		this.findPageBySql(TSalaryInfo.class, sqlBuilder.toString(), page, arrayList.toArray());
	}

	@Override
	public List<TSalaryInfo> getListSalaryInfoQuery(String baseOrgId, String userName, String erpNo, Integer year, String auditStatus) {
		ArrayList<Object> arrayList = new ArrayList<Object>();
		String sql = "select distinct TEBI.id AS id,TEBI.NAME AS user_name,TEBI.erp_no AS erp_no,a.year as year,case " +
				" when a.erp_no is not null then '已申报' " +
				" else '未申报'" +
				" end as audit_status FROM t_emp_base_info TEBI " +
				" LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no " +
				" LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id " +
				" LEFT JOIN t_org_dept TOD2 ON TOD2.id = TDER.org_id " +
				" left join t_salary_info a on a.del_sign = 0  and a.erp_no=TEBI.erp_no and a.audit_status!='2' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		if (null!=year) {
			sqlBuilder.append(" AND a.year =? ");
			arrayList.add(year);
		}
		sqlBuilder.append(" WHERE TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
		sqlBuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
		arrayList.add(baseOrgId);
		if (StringUtils.isNotBlank(userName)) {
			sqlBuilder.append(" AND TEBI.NAME LIKE concat('%',?,'%') ");
			arrayList.add(userName);
		}
		if (StringUtils.isNotBlank(erpNo)) {
			sqlBuilder.append(" AND TEBI.erp_no LIKE concat('%',?,'%') ");
			arrayList.add(erpNo);
		}
		/*if (null!=year) {
			sqlBuilder.append(" AND a.year =? ");
			arrayList.add(year);
		}*/
		if (StringUtils.isNotBlank(auditStatus)) {
			if(auditStatus.equals("0")){
				//已申报
				sqlBuilder.append(" AND a.erp_no is not null ");
			}else{
                //未申报
				sqlBuilder.append(" AND a.erp_no is null ");
			}
		}
		sqlBuilder.append(" order by a.year desc,a.erp_no desc,TOD.id ");
		return this.findAllBySQL(TSalaryInfo.class, sqlBuilder.toString(),null,false,arrayList.toArray());
	}


	@Override
	public List<TSalaryInfo> judge(TSalaryInfo salaryInfo) {
		StringBuilder sqlBuilder = new StringBuilder(
				"select id FROM t_salary_info where 1=1 ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		sqlBuilder.append(" and erp_no=? ");
		arrayList.add(salaryInfo.getErpNo());
		sqlBuilder.append(" and year=? ");
		arrayList.add(salaryInfo.getYear());
		return this.findAllBySQL(TSalaryInfo.class, sqlBuilder.toString(), null, false, arrayList.toArray());
	}

	public List<DealFileVo> getFilesByBusinessId(String businessId, String businessNode) {
		StringBuilder sqlBuilder = new StringBuilder(
				"select business_id,file_id,file_name,business_node,business_type_id,order_num ");
		sqlBuilder.append(" from t_file where 1=1 ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(businessId)) {
			sqlBuilder.append(" and business_id=?");
			arrayList.add(businessId);
		}
		if (!StringUtil.isNullOrEmpty(businessNode)) {
			sqlBuilder.append(" and business_node=?");
			arrayList.add(businessNode);
		}
		sqlBuilder.append(" order by order_num ");
		return this.findAllBySQL(DealFileVo.class, sqlBuilder.toString(), null, false,arrayList.toArray());
	}

	@Override
	public void updateSalaryState(String id, String state,String auditOpt) {
		String sql = "update t_salary_info set  ";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" audit_status = #{audit_status,jdbcType=VARCHAR}, ");
		sqlBuilder.append(" audit_opt = #{audit_opt,jdbcType=VARCHAR} ");
		sqlBuilder.append(" where 1=1 ");
		Map<String, Object> params = new HashMap<String, Object>();
		sqlBuilder.append(" and id=#{id,jdbcType=VARCHAR}");
		params.put("audit_status",state);
		params.put("audit_opt",auditOpt);
		params.put("id",id);
		this.executeSql(sqlBuilder.toString(), params);
	}

	@Override
	public TSalaryInfo selectTSalaryInfoById(String id){
		StringBuilder sqlBuilder = new StringBuilder(
				"select t.* FROM t_salary_info t");
		sqlBuilder.append(" where t.id=? ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		arrayList.add(id);
		return this.findUnique(TSalaryInfo.class, sqlBuilder.toString(), arrayList.toArray());
	}

	@Override
	public List<User> searchUserListByOrgId(String baseOrgId) {
		StringBuilder sqlbuilder = new StringBuilder(" SELECT TEBI.id as ID,TEBI.NAME as NAME,TEBI.erp_no as FAX,TOD.id as dept_id,TOD.NAME as ORG_ANISATION_NAME,TDER.org_id as BASE_ORG_ID,TOD2.name as BASE_ORG_NAME");
		sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id  LEFT JOIN t_org_dept TOD2 ON TOD2.id = TDER.org_id");
		sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
		arrayList.add(baseOrgId);
		return this.findAllBySQL(User.class, sqlbuilder.toString(), null, false, arrayList.toArray());
	}

	@Override
	public List<User> searchUserByErpNo(String baseOrgId,String erpNo) {
		StringBuilder sqlbuilder = new StringBuilder(" SELECT TEBI.id as ID,TEBI.NAME as NAME,TEBI.erp_no as FAX,TOD.id as dept_id,TOD.NAME as ORG_ANISATION_NAME,TDER.org_id as BASE_ORG_ID,TOD2.name as BASE_ORG_NAME");
		sqlbuilder.append(" FROM t_emp_base_info TEBI LEFT JOIN t_dept_emp_rela TDER ON TEBI.erp_no = TDER.erp_no LEFT JOIN t_org_dept TOD ON TOD.id = TDER.dept_id  LEFT JOIN t_org_dept TOD2 ON TOD2.id = TDER.org_id");
		sqlbuilder.append(" WHERE 1=1 AND TEBI.del_sign = 0 AND TEBI.is_valid = 0 ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		sqlbuilder.append(" AND TOD.path LIKE concat('%',?,'%') ");
		sqlbuilder.append(" AND TEBI.erp_no = ? ");
		arrayList.add(baseOrgId);
		arrayList.add(erpNo);
		return this.findAllBySQL(User.class, sqlbuilder.toString(), null, false, arrayList.toArray());
	}

	@Override
	public List<TSalaryInfo> searchNoPointSalaryListByOrgId(String baseOrgId,Integer year) {
		StringBuilder sqlbuilder = new StringBuilder(" SELECT tsi.* from t_salary_info tsi ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		sqlbuilder.append(" left join t_person_year_point_detail tpyp on tpyp.salary_id = tsi.id ");
		sqlbuilder.append(" where tpyp.salary_id is null and tsi.year = ? and tsi.org_id = ? ");
		arrayList.add(year);
		arrayList.add(baseOrgId);
		return this.findAllBySQL(TSalaryInfo.class, sqlbuilder.toString(), null, false, arrayList.toArray());
	}

}
